﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Xml;
using System.Data;
using System.IO;
using System.Data.SqlClient;
using System.Data.Common;

namespace Ruandao.MiniAccess.SqlServer
{

    /// <summary>
    /// 
    /// </summary>
    public abstract class SqlCommandBase
    {
        /// <summary>
        /// 
        /// </summary>
        protected SqlCommandFile sqlCommandFile;

        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlCommandFile"></param>
        public SqlCommandBase(SqlCommandFile sqlCommandFile)
        {
            this.sqlCommandFile = sqlCommandFile;
        }

        /// <summary>
        /// 
        /// </summary>
        abstract public void Execute();

        protected byte[] ReadBinaryFromFile(string file)
        {
            string pathfile = Path.Combine(sqlCommandFile.CurrentPath, file);
            if (!File.Exists(pathfile))
            {
                return null;
            }
            byte[] bytes = null;
            using (FileStream stream = new FileStream(pathfile, FileMode.Open, FileAccess.Read))
            {
                using (BinaryReader reader = new BinaryReader(stream))
                {
                    bytes = reader.ReadBytes((int)stream.Length);
                }
            }
            return bytes;
        }

        protected string ReadTextFromFile(string file)
        {
            string pathfile = Path.Combine(sqlCommandFile.CurrentPath, file);
            if (!File.Exists(pathfile))
            {
                return "";
            }
            string text = null;
            using (FileStream stream = new FileStream(pathfile, FileMode.Open, FileAccess.Read))
            {
                using (StreamReader reader = new StreamReader(stream))
                {
                    text = reader.ReadToEnd();
                }
            }
            return text;
        }
    }

    /// <summary>
    /// 用Delete语句删除数据库表中的数据。
    /// </summary>
    public class DeleteCommand : SqlCommandBase
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlCommandFile"></param>
        /// <param name="node"></param>
        public DeleteCommand(SqlCommandFile sqlCommandFile, XmlNode node)
            : base(sqlCommandFile)
        {
            SetFromXmlNode(node);
        }

        /// <summary>
        /// 
        /// </summary>
        public override void Execute()
        {
            string commandText = "DELETE " + table;
            if (!string.IsNullOrEmpty(condition))
            {
                commandText += " WHERE " + condition;
            }
            this.sqlCommandFile.GetDatabase().ExecuteNonQuery(CommandType.Text, commandText);
        }

        string condition;
        string table;
        private void SetFromXmlNode(XmlNode node)
        {
            this.table = node.Attributes["table"].Value;
            this.condition = node.InnerText;
        }

    }

    /// <summary>
    /// 用Truncate语句清空数据库表中的所有内容。
    /// </summary>
    public class TruncateCommand : SqlCommandBase
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlCommandFile"></param>
        /// <param name="node"></param>
        public TruncateCommand(SqlCommandFile sqlCommandFile, XmlNode node)
            : base(sqlCommandFile)
        {
            SetFromXmlNode(node);
        }

        /// <summary>
        /// 
        /// </summary>
        public override void Execute()
        {
            string commandText = "TRUNCATE TABLE " + table;
            this.sqlCommandFile.GetDatabase().ExecuteNonQuery(CommandType.Text, commandText);
        }

        string table;
        private void SetFromXmlNode(XmlNode node)
        {
            this.table = node.Attributes["table"].Value;
        }

    }

    /// <summary>
    /// 用Truncate语句清空数据库表中的所有内容。
    /// </summary>
    public class ScriptCommand : SqlCommandBase
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlCommandFile"></param>
        /// <param name="node"></param>
        public ScriptCommand(SqlCommandFile sqlCommandFile, XmlNode node)
            : base(sqlCommandFile)
        {
            SetFromXmlNode(node);
        }

        /// <summary>
        /// 
        /// </summary>
        public override void Execute()
        {
            using (SqlCommand cmd = new SqlCommand("EXECUTE ( @script )"))
            {
                cmd.Parameters.Add(new SqlParameter("@script", scriptText));
                this.sqlCommandFile.GetDatabase().ExecuteNonQuery(cmd);
            }
        }

        string scriptText;
        private void SetFromXmlNode(XmlNode node)
        {
            XmlAttribute fileAttribute = node.Attributes["file"];
            if ( fileAttribute != null)
            {
                this.scriptText = ReadTextFromFile(fileAttribute.Value);
                return;
            }

            // 读取CDATA中的数据
            foreach (XmlNode sub in node.ChildNodes)
            {
                if (sub.NodeType == XmlNodeType.CDATA)
                {
                    this.scriptText = sub.InnerText;
                    break;
                }
            }
        }

    }


    /// <summary>
    /// 使用Insert语句向数据库表中添加记录。
    /// 如果使用了Update列，而且表有主键（支持符合主键），那么主键值必须包含在Insert语句中。
    /// 目前暂不实现的功能：如果使用了Update列，而表没有主键，那么使用所有列的值来查找记录。
    /// </summary>
    public class InsertCommand : SqlCommandBase
    {
        /// <summary>
        /// 
        /// </summary>
        /// <param name="sqlCommandFile"></param>
        /// <param name="node"></param>
        public InsertCommand(SqlCommandFile sqlCommandFile, XmlNode node)
            : base(sqlCommandFile)
        {
            SetFromXmlNode(node);
        }

        /// <summary>
        /// 
        /// </summary>
        public override void Execute()
        {
            string command ="";
            if (IncludeIdentityColumn())
            {
                command += "SET IDENTITY_INSERT " + table + " ON;";
            }

            command += " INSERT " + table + " (" + columns + ")";
            command += " VALUES ( " + values + " );";

            if (IncludeIdentityColumn())
            {
                command += "SET IDENTITY_INSERT " + table + " OFF;";
            }

            sqlCommandFile.GetDatabase().ExecuteNonQuery(CommandType.Text,command);

            ExecuteUpdate();
        }

        string table;
        string columns;
        string values;
        List<KeyValuePair<string, string>> updateColumns = new List<KeyValuePair<string, string>>();

        // 参数节点对应的是Values节点，而不是inset节点。
        private void SetFromXmlNode(XmlNode node)
        {
            this.table = node.ParentNode.Attributes["table"].Value;
            this.columns = node.ParentNode.Attributes["columns"].Value;

            foreach ( XmlNode subNode in node.ChildNodes)
            {
                switch( subNode.LocalName )
                {
                    case "text":
                        this.values = subNode.InnerText;
                        break;
                    case "update":
                        string column = subNode.Attributes["column"].Value;
                        string file = subNode.Attributes["file"].Value;
                        updateColumns.Add(new KeyValuePair<string,string>(column,file));
                        break;
                }
            }
        }

        private void ExecuteUpdate()
        {
            if (updateColumns.Count == 0) return;

            Database database = sqlCommandFile.GetDatabase();
            TableMap tm = sqlCommandFile.GetTableMap(table);

            using (DbCommand command = database.GetSqlStringCommand(GetUpdateCommand()))
            {
                foreach (KeyValuePair<string, string> column in updateColumns)
                {
                    object value;
                    if (IsText(column.Key))
                    {
                        value = ReadTextFromFile(column.Value);
                    }
                    else
                    {
                        value = ReadBinaryFromFile(column.Value);
                    }
                    database.AddInParameter(command, column.Key, tm.GetColumn(column.Key).DbType, value);
                }
                sqlCommandFile.GetDatabase().ExecuteNonQuery(command);
            }
            
        }

        private bool IsText(string column)
        {
            ColumnMap col=null;
            foreach( ColumnMap c in sqlCommandFile.GetTableMap(this.table).ColumnMaps)
            {
                if (c.ColumnName == column)
                {
                    col = c;
                    break;
                }
            }
            if (col == null)
            {
                throw new SqlCommandFormatException("");
            }
            switch (col.Type.ToLower())
            {
                case "text":
                case "ntext":
                case "nchar":
                case "nvarchar":
                    return true;
                case "image":
                case "binary":
                case "varbinary":
                    return false;
                default: 
                    throw new NotSupportedException("类型为<" + col.Type + ">的字段值不支持从文件中读取。");
            }
        }

        private string GetUpdateCommand()
        {
            IList<ColumnMap> primaryKeys = sqlCommandFile.GetTableMap(this.table).PrimaryKeyColumns;
            if (primaryKeys.Count == 0)
            {
                throw new SqlCommandFormatException("");
            }

            string command = " UPDATE " + this.table + " SET ";
            foreach (KeyValuePair<string, string> column in updateColumns)
            {
                command += column.Key + "=@" + column.Key + ",";
            }
            // 去掉最后面的一个逗号: ","。
            command = command.Substring(0, command.Length - 1);
            command += GetCondition(primaryKeys);
            return command;
        }

        private string GetCondition( IList<ColumnMap> primaryKeys )
        {
            string condition = " WHERE ";

            foreach ( ColumnMap primaryKey in primaryKeys )
            {
                if (!ColumnValueDictionary.ContainsKey(primaryKey.ColumnName))
                {
                    throw new SqlCommandFormatException("");
                }
                condition += primaryKey.ColumnName + "=" + ColumnValueDictionary[primaryKey.ColumnName] + " AND ";
            }
            // 去掉最后面的5个字符：" AND "。
            condition = condition.Substring(0, condition.Length - 5);
            return condition;
        }

        // 把columns和values中的内容分解到一个名值对的列表中。
        private Dictionary<string,string> ColumnValueDictionary
        {
            get
            {
                Dictionary<string,string> list = new Dictionary<string,string>();
                string[] columnArray = columns.Split(',');
                string[] valueArray = values.Split(',');
                if ( columnArray.Length != valueArray.Length )
                {
                    throw new SqlCommandFormatException("");
                }
                for( int i=0; i<columnArray.Length; i++)
                {
                    list.Add(columnArray[i], valueArray[i]);
                }
                return list;
            }

        }

        // 如果insert语句中包含了标识列，则返回true，否则返回false。
        private bool IncludeIdentityColumn()
        {
            string identity = sqlCommandFile.GetTableMap(this.table).IdentityColumn;
            if (!string.IsNullOrEmpty(identity))
            {
                if (ColumnValueDictionary.ContainsKey(identity))
                {
                    return true;
                }
            }
            return false;
        }

    }

    /// <summary>
    /// 读取并解释sql.xml文件。
    /// 在目前的实现中，先不过多的检测xml格式的正确性。
    /// 在未来版本的实现中使用架构的方式在Load的时候就一次性的验证格式的正确性。
    /// </summary>
    public class SqlCommandFile
    {
        private IList<SqlCommandBase> commandList = new List<SqlCommandBase>();

        /// <summary>
        /// 构造函数。
        /// </summary>
        /// <param name="pathFile"></param>
        public SqlCommandFile(string pathFile)
        {
            ReadFromXmlFile(pathFile);
        }

        /// <summary>
        /// 执行SqlCommandFile中的所有命令。
        /// </summary>
        public void ExecuteCommand()
        {
            foreach (SqlCommandBase cmd in commandList)
            {
                cmd.Execute();
            }
        }

        string connectionString;
        /// <summary>
        /// 
        /// </summary>
        public string ConnectionString
        {
            get { return this.connectionString; }
        }

        string currentPath;
        /// <summary>
        /// 
        /// </summary>
        public string CurrentPath
        {
            get { return this.currentPath; }
        }
        
        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public Database GetDatabase()
        {
            return new SqlDatabase(connectionString);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public TableMap GetTableMap(string tableName)
        {
            //return new TableSchema(tableName,GetDatabase());
            return new TableMap(tableName,GetDatabase() );
        }

        private void ReadFromXmlFile(string pathFile)
        {
            XmlDocument xmlDoc = new XmlDocument();
            xmlDoc.Load(pathFile);

            XmlNode rootNode = xmlDoc.DocumentElement;

            if (rootNode.Name != "dataBase")
            {
                throw new Exception();
            }

            connectionString = rootNode.Attributes["connectionString"].Value;
            if (string.IsNullOrEmpty(connectionString))
            {
                throw new Exception();
            }

            // GetDirectoryName的意思是去掉字符串中最后一个斜杠及之后的字符。
            this.currentPath = Path.GetDirectoryName(pathFile);
            
            foreach ( XmlNode  subNode in rootNode.ChildNodes)
            {
                switch (subNode.Name.ToLower())
                {
                    case "delete":
                        commandList.Add(new DeleteCommand(this,subNode));
                        break;
                    case "truncate":
                        commandList.Add(new TruncateCommand(this, subNode));
                        break;
                    case "script":
                        commandList.Add(new ScriptCommand(this, subNode));
                        break;
                    case "insert":
                        foreach (XmlNode valuesNode in subNode.ChildNodes)
                        {
                            if (valuesNode.LocalName == "values")
                            {
                                commandList.Add(new InsertCommand(this, valuesNode));
                            }
                        }
                        break;
                    default:
                        // 忽略其他不认识的子元素。
                        break;
                }
            }
        }

    }// end of class
}


